package cn.com.dashihui.web.service;

import java.util.ArrayList;
import java.util.List;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.com.dashihui.web.dao.Seller;
import cn.com.dashihui.web.dao.User;

public class SellerService {
	/**
	 *公司并返回到首页。
	 */
	public boolean addSeller(Seller newObject){
		return newObject.save();
	}
	
	/**
	 * 查询公司详情
	 * @param id
	 * @return
	 */
	public boolean FindExistsById(String sellerId) {
		return Seller.me().findFirst("select * from t_bus_seller where sellerId=?",sellerId)!=null;
	}
	public Record FindById(int userId) {
		return Db.findFirst("select * from t_bus_seller where userid=?",userId);
	}
	public Record detail(String sellerId,int userid) {
		return Db.findFirst("select * from t_bus_seller where sellerId=? and userid=?",sellerId,userid);
	}
	public Seller findBySellerId(String sellerId) {
		return Seller.me().findFirst("select * from t_bus_seller where sellerId=?",sellerId);
	}
	
	public Record findByUserId(int userid){
		return Db.findFirst("select sellerId from t_bus_seller where userid=?",userid);
	}
	

	
	/**
	 * 查询分公司的storeid
	 * @return
	 */
	public Record findStoreState(int userid) {
		return Db.findFirst("SELECT  A.storeid from  t_dict_store as A inner JOIN t_auth_admin_seller as B ON A.storeid=B.storeid where B.id=?",userid);
	}
	public Record countNumber(String sellerId) {
		return Db.findFirst("SELECT ifnull(count(A.orderNum),0) as orderNum,ifnull(sum(A.amount),0) as amount FROM t_bus_order A "
				+ " left JOIN t_bus_order_list Li ON Li.orderNum = A.orderNum "
				+ " left JOIN t_bus_goods D ON D.id = Li.goodsid "
				+ " left JOIN t_bus_goods_store_rel E ON E.goodstore = D.good_store "
				+ " left JOIN t_dict_store B ON E.storeid = B.storeid "
				+ " left JOIN t_bus_seller C on B.sellerId=C.sellerId where A.orderState NOT IN(1,5,7,9) and C.sellerId= ?",sellerId);
	}
	public Record countNumbergb(String sellerId) {
		return Db.findFirst("SELECT ifnull(count(A.orderNum),0) as orderNum,ifnull(sum(A.amount),0) as amount FROM t_bus_order_gb A "
				+ " left JOIN t_bus_goods D ON D.id = A.goodsid "
				+ " left JOIN t_bus_goods_store_rel E ON E.goodstore = D.good_store "
				+ " left JOIN t_dict_store B ON E.storeid = B.storeid "
				+ " left JOIN t_bus_seller C on B.sellerId=C.sellerId where A.orderState NOT IN(1,5,7,9) and C.sellerId= ?",sellerId);
	}
	public Record countNumberAdmin() {
		return Db.findFirst("SELECT ifnull(count(orderNum),0) as orderNum,ifnull(sum(amount),0) as amount"
				+ " FROM t_bus_order A  where orderState NOT IN(1,5,7,9) ");
	}
	public Record countNumberAdmingb() {
		return Db.findFirst("SELECT ifnull(count(A.orderNum),0) as orderNum,ifnull(sum(amount),0) as amount"
				+ " FROM t_bus_order_gb A  where orderState NOT IN(1,5,7,9) ");
	}
	public Record countstoreidNumber(String storeid) {
		return Db.findFirst("SELECT ifnull(count(A.orderNum),0) as orderNum ,ifnull(sum(A.amount),0) as amount FROM t_bus_order A "
				+ " left JOIN t_bus_order_list Li ON Li.orderNum = A.orderNum "
				+ " left JOIN t_bus_goods D ON D.id = Li.goodsid "
				+ " left JOIN t_bus_goods_store_rel E ON E.goodstore = D.good_store "
				+ " left JOIN t_dict_store B ON E.storeid = B.storeid "
				+ " left JOIN t_bus_seller C on B.sellerId=C.sellerId where A.orderState NOT IN(1,5,7,9) and B.storeid= ?",storeid);
	}
	public Record countstoreidNumbergb(String storeid) {
		return Db.findFirst("SELECT ifnull(count(A.orderNum),0) as orderNum ,ifnull(sum(A.amount),0) as amount FROM t_bus_order_gb A "
				+ " left JOIN t_bus_goods D ON D.id = A.goodsid "
				+ " left JOIN t_bus_goods_store_rel E ON E.goodstore = D.good_store "
				+ " left JOIN t_dict_store B ON E.storeid = B.storeid "
				+ " left JOIN t_bus_seller C on B.sellerId=C.sellerId where A.orderState NOT IN(1,5,7,9) and B.storeid= ?",storeid);
	}
	/**
	 * 根据公司userid查询分公司
	 */
	public List<Record> findAllStoreid(int userid) {
		return Db.find("select storeid,title from t_dict_store where sellerId=(select sellerId from t_bus_seller where userid=?)",userid);
	}

	/**
	 * 查询分页数据
	 * @param pageNum
	 * @param pageSize
	 * @return
	 */
	
	public Page<Record> findByTotle(int RoleId,int pageNum, int pageSize,String sellerId,String storeid){
		StringBuffer sBuffer = new StringBuffer(" from t_obj_date  A "
				+ "left join (select count(C.orderNum) as orderNum1 , sum(C.amount) as amount1 ,DATE_FORMAT(C.startDate,'%y-%m-%d') startDate1 "
					+ "from t_bus_order_list LI	"
					+ "left join t_bus_order C on LI.orderNum = C.orderNum "
					+ "left join t_bus_goods G on LI.goodsid = G.id "
					+ "left join t_bus_goods_store_rel D on D.goodstore = G.good_store "
					+ " left join t_dict_store M ON M.storeid = D.storeid  "
					+ " where  C.orderState not in (1,5,7,9) ");
		List<Object> params = new ArrayList<Object>(); 
		if(RoleId==1){
			sBuffer.append(" and 1=1 ");
		}else{
		sBuffer.append(" AND M.sellerId=? ");
		params.add(sellerId);
		 if(!StrKit.isBlank(storeid)){
				sBuffer.append(" AND M.storeid=? ");
				params.add(storeid);
		  }
		}
		sBuffer.append(" group by DATE_FORMAT(C.startDate,'%y-%m-%d') ) B on A.RepDate = B.startDate1 ");
		sBuffer.append("left join (select count(C.orderNum) as orderNum1 , sum(C.amount) as amount1 ,DATE_FORMAT(C.startDate,'%y-%m-%d') startDate1 "
					+ "from t_bus_order_gb C  "
					+ "left join t_bus_goods G on C.goodsid = G.id "
					+ "left join t_bus_goods_store_rel D on D.goodstore = G.good_store "
					+ " left join t_dict_store M ON M.storeid = D.storeid  "
					+ " where  C.orderState not in (1,5,7,9) ");
		if(RoleId==1){
			sBuffer.append(" and 1=1 ");
		}else{
		sBuffer.append(" AND M.sellerId=? ");
		params.add(sellerId);
		 if(!StrKit.isBlank(storeid)){
				sBuffer.append(" AND M.storeid=? ");
				params.add(storeid);
		  }
		}
		sBuffer.append(" group by DATE_FORMAT(C.startDate,'%y-%m-%d') ) C on A.RepDate = C.startDate1 ");
		sBuffer.append(" order by A.RepDate desc ");
		return Db.paginate(pageNum, pageSize, "select A.*,ifnull(B.orderNum1,0) as orderNum ,ifnull(B.amount1,0) as amount,ifnull(C.orderNum1,0) as orderNumgb ,ifnull(C.amount1,0) as amountgb ", sBuffer.toString(), params.toArray());
	}
	

	/**
	 * 根据userid,获取角色
	 * @param userid
	 * @return
	 */
	public int findByRoleId(int userid){
		return Db.queryInt("SELECT C.id  from t_auth_admin_seller as A inner JOIN  t_auth_admin_roles_seller as B ON A.id=B.userid  INNER JOIN t_auth_roles_seller AS C ON B.roleid = C.id where A.id=?" ,userid);
	}

	public boolean editBySellerId(String sellerId) {
		return Db.update("update t_bus_seller set state=4 where sellerId=?", sellerId)==1;
	}

	/**
	 * 查询当前手机号是否已在前端注册
	 * @param id
	 * @return
	 */
	public boolean findUser(String contact) {
		return User.me().findFirst("select * from t_bus_user where phone=?",contact)!=null;
	}
	
}
